﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
using VOL.Core.DbSqlSugar;
using VOL.demo.IRepositories.ReturnOrder;
using VOL.Entity.DomainModels;

namespace VOL.demo.Repositories.ReturnOrder
{
    public class ReturnOrderRepository : IReturnOrderIRepository
    {
        private readonly ISqlSugarClient _dbContext;

        public ReturnOrderRepository()
        {
            _dbContext = DbManger.Db; // 使用默认的数据库连接
        }

        /// <summary>
        /// 获取SqlSugar客户端
        /// </summary>
        public ISqlSugarClient DbContext => _dbContext;

        /// <summary>
        /// 根据ID获取退货单
        /// </summary>
        /// <param name="id">退货单ID</param>
        /// <returns>退货单信息</returns>
        public async Task<returnordermodel> GetByIdAsync(long id)
        {
            return await _dbContext.Queryable<returnordermodel>()
                .Where(x => x.Id == id && x.IsDelete == 0)
                .FirstAsync();
        }

        /// <summary>
        /// 获取所有退货单
        /// </summary>
        /// <returns>退货单列表</returns>
        public async Task<List<returnordermodel>> GetAllAsync()
        {
            return await _dbContext.Queryable<returnordermodel>()
                .Where(x => x.IsDelete == 0)
                .ToListAsync();
        }

        /// <summary>
        /// 分页查询退货单
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>分页结果</returns>
        public async Task<(List<returnordermodel> Data, int TotalCount)> GetPageListAsync(int pageIndex, int pageSize, 
            Expression<Func<returnordermodel, bool>> whereExpression = null)
        {
            var query = _dbContext.Queryable<returnordermodel>()
                .Where(x => x.IsDelete == 0);
                
            if (whereExpression != null)
            {
                query = query.Where(whereExpression);
            }

            RefAsync<int> totalCount = 0;
            var data = await query
                .OrderByDescending(x => x.CreateDate)
                .ToPageListAsync(pageIndex, pageSize, totalCount);
                
            return (data, totalCount);
        }
        /// <summary>
        /// 更新退货单
        /// </summary>
        /// <param name="entity">退货单实体</param>
        /// <returns>更新结果</returns>
        public async Task<bool> UpdateAsync(returnordermodel entity)
        {
            entity.ModifyDate = DateTime.Now;
            
            var result = await _dbContext.Updateable(entity)
                .IgnoreColumns(ignoreAllNullColumns: true)
                .ExecuteCommandAsync();
                
            return result > 0;
        }

        /// <summary>
        /// 批量更新退货单
        /// </summary>
        /// <param name="entities">退货单实体列表</param>
        /// <returns>更新结果</returns>
        public async Task<bool> UpdateRangeAsync(List<returnordermodel> entities)
        {
            foreach (var entity in entities)
            {
                entity.ModifyDate = DateTime.Now;
            }
            
            var result = await _dbContext.Updateable(entities)
                .IgnoreColumns(ignoreAllNullColumns: true)
                .ExecuteCommandAsync();
                
            return result > 0;
        }

        /// <summary>
        /// 删除退货单(软删除)
        /// </summary>
        /// <param name="id">退货单ID</param>
        /// <returns>删除结果</returns>
        public async Task<bool> DeleteAsync(long id)
        {
            var entity = await GetByIdAsync(id);
            if (entity == null)
            {
                return false;
            }
            
            entity.IsDelete = 1;
            entity.ModifyDate = DateTime.Now;
            
            var result = await _dbContext.Updateable(entity)
                .UpdateColumns(x => new { x.IsDelete, x.ModifyDate })
                .ExecuteCommandAsync();
                
            return result > 0;
        }

        /// <summary>
        /// 批量删除退货单(软删除)
        /// </summary>
        /// <param name="ids">退货单ID列表</param>
        /// <returns>删除结果</returns>
        public async Task<bool> DeleteRangeAsync(long[] ids)
        {
            var result = await _dbContext.Updateable<returnordermodel>()
                .SetColumns(x => new returnordermodel 
                { 
                    IsDelete = 1, 
                    ModifyDate = DateTime.Now 
                })
                .Where(x => ids.Contains(x.Id))
                .ExecuteCommandAsync();
                
            return result > 0;
        }

        /// <summary>
        /// 根据条件查询退货单
        /// </summary>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>退货单列表</returns>
        public async Task<List<returnordermodel>> QueryAsync(Expression<Func<returnordermodel, bool>> whereExpression)
        {
            return await _dbContext.Queryable<returnordermodel>()
                .Where(whereExpression)
                .Where(x => x.IsDelete == 0)
                .ToListAsync();
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>查询结果</returns>
        public async Task<List<returnordermodel>> QueryBySqlAsync(string sql, object parameters = null)
        {
            return await _dbContext.Ado.SqlQueryAsync<returnordermodel>(sql, parameters);
        }
        
        /// <summary>
        /// 获取进货单联查列表（包含供应商名称、部门名称、客户名称）
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <returns>分页结果</returns>
        public async Task<(List<PurchaseOrderWithRelation> Data, int TotalCount)> GetPurchaseOrderListAsync(int pageIndex, int pageSize)
        {
            RefAsync<int> totalCount = 0;
            
            var data = await _dbContext.Queryable<PurchaseOrderModel>()
                .LeftJoin<suppliermodel>((po, s) => po.SupplierId == s.Id)
                .LeftJoin<Sys_Department>((po, s, d) => po.DeptId == SqlFunc.ToString(d.DepartmentId))
                .LeftJoin<customermodel>((po, s, d, c) => SqlFunc.ToInt64(po.UserId) == c.Id)
                .Where((po, s, d, c) => po.IsDel == 0)
                .OrderByDescending((po, s, d, c) => po.CreateDate)
                .Select((po, s, d, c) => new PurchaseOrderWithRelation
                {
                    // 进货单字段
                    Id = po.Id,
                    OrderCode = po.OrderCode,
                    OrderDate = po.OrderDate,
                    SupplierId = po.SupplierId,
                    Contact = po.Contact,
                    ContactPhone = po.ContactPhone,
                    DeptId = po.DeptId,
                    UserId = po.UserId,
                    Status = po.Status,
                    AuditUser = po.AuditUser,
                    AuditTime = po.AuditTime,
                    Remarks = po.Remarks,
                    IsDel = po.IsDel,
                    Creator = po.Creator,
                    CreateDate = po.CreateDate,
                    Modifier = po.Modifier,
                    ModifyDate = po.ModifyDate,
                    CreateID = po.CreateID,
                    ModifyID = po.ModifyID,
                    
                    // 关联表字段
                    SupplierName = s.SupplierName,
                    DepartmentName = d.DepartmentName,
                    CustomerName = c.CustomerName
                })
                .ToPageListAsync(pageIndex, pageSize, totalCount);
                
            return (data, totalCount);
        }
        
        /// <summary>
        /// 根据进货单编号获取进货单信息（包含供应商、联系人、联系方式、部门、进货人）
        /// </summary>
        /// <param name="orderCode">进货单编号</param>
        /// <returns>进货单信息</returns>
        public async Task<PurchaseOrderWithRelation> GetPurchaseOrderByCodeAsync(string orderCode)
        {
            if (string.IsNullOrEmpty(orderCode))
            {
                return null;
            }
            
            return await _dbContext.Queryable<PurchaseOrderModel>()
                .LeftJoin<suppliermodel>((po, s) => po.SupplierId == s.Id)
                .LeftJoin<Sys_Department>((po, s, d) => po.DeptId == SqlFunc.ToString(d.DepartmentId))
                .LeftJoin<customermodel>((po, s, d, c) => SqlFunc.ToInt64(po.UserId) == c. Id)
                .Where((po, s, d, c) => po.OrderCode == orderCode && po.IsDel == 0)
                .Select((po, s, d, c) => new PurchaseOrderWithRelation
                {
                    // 进货单字段
                    Id = po.Id,
                    OrderCode = po.OrderCode,
                    OrderDate = po.OrderDate,
                    SupplierId = po.SupplierId,
                    Contact = po.Contact,
                    ContactPhone = po.ContactPhone,
                    DeptId = po.DeptId,
                    UserId = po.UserId,
                    Status = po.Status,
                    AuditUser = po.AuditUser,
                    AuditTime = po.AuditTime,
                    Remarks = po.Remarks,
                    IsDel = po.IsDel,
                    Creator = po.Creator,
                    CreateDate = po.CreateDate,
                    Modifier = po.Modifier,
                    ModifyDate = po.ModifyDate,
                    CreateID = po.CreateID,
                    ModifyID = po.ModifyID,
                    
                    // 关联表字段
                    SupplierName = s.SupplierName,
                    DepartmentName = d.DepartmentName,
                    CustomerName = c.CustomerName
                })
                .FirstAsync();
        }
        
        /// <summary>
        /// 根据进货单号获取进货明细列表（包含货品信息）
        /// </summary>
        /// <param name="orderCode">进货单号</param>
        /// <returns>进货明细列表</returns>
        public async Task<List<PurchaseOrderItemWithProductModel>> GetPurchaseOrderItemsByCodeAsync(string orderCode)
        {
            if (string.IsNullOrEmpty(orderCode))
            {
                return new List<PurchaseOrderItemWithProductModel>();
            }
            
            // 先查询进货单ID
            var purchaseOrder = await _dbContext.Queryable<PurchaseOrderModel>()
                .Where(po => po.OrderCode == orderCode && po.IsDel == 0)
                .FirstAsync();
                
            if (purchaseOrder == null)
            {
                return new List<PurchaseOrderItemWithProductModel>();
            }
            
            // 根据进货单ID查询明细
            return await _dbContext.Queryable<PurchaseOrderItemModel>()
                .LeftJoin<productinformationmodel>((poi, p) => poi.GoodsId == p.Id)
                .Where((poi, p) => poi.OrderCode == purchaseOrder.OrderCode && poi.IsDel == 0)
                .Select((poi, p) => new PurchaseOrderItemWithProductModel
                {
                    // 进货明细字段
                    Id = poi.Id,
                    PurchaseOrderId = purchaseOrder.Id,
                    OrderCode = poi.OrderCode,
                    Quantity = poi.PurchaseQty,
                    UnitPrice = poi.PurchasePrice,
                    TotalAmount = SqlFunc.ToDecimal(poi.Amount),
                    OutboundQuantity = 0, // 这里需要根据实际情况获取出库数量
                    UnoutboundQuantity = poi.PurchaseQty, // 这里需要根据实际情况计算未出库数量
                    Remarks = poi.ItemRemarks,
                    
                    // 货品信息字段
                    ProductId = poi.GoodsId,
                    ProductCode = p.goodsCode,
                    ProductName = p.goodsName,
                    ProductType = SqlFunc.ToString(p.productTypeId), // 这里使用货品类型ID
                    SpecificationModel = p.specModel,
                    Unit = p.shelfLifeUnit, // 这里临时使用保质期单位字段
                    Barcode = "", // 这里暂时为空，因为货品表中没有条码字段
                    StockQuantity = p.currentInventory
                })
                .ToListAsync();
        }

        /// <summary>
        /// 获取退货单联查结果（包含供应商名称、部门名称、客户名称）
        /// </summary>
        /// <param name="id">退货单ID</param>
        /// <returns>退货单联查结果</returns>
        public async Task<ReturnOrderWithRelations> GetWithRelationsAsync(long id)
        {
            // 检查部门ID是否存在
            var returnOrder = await _dbContext.Queryable<returnordermodel>().FirstAsync(r => r.Id == id);
            if (returnOrder != null)
            {
                var deptExists = await _dbContext.Queryable<Sys_Department>()
                    .AnyAsync(d => SqlFunc.ToString(d.DepartmentId) == returnOrder.DeptId);
                
                var customerExists = await _dbContext.Queryable<customermodel>()
                    .AnyAsync(c => c.Id == returnOrder.CustomerId);
                
                Console.WriteLine($"DeptId: {returnOrder.DeptId}, Exists: {deptExists}");
                Console.WriteLine($"CustomerId: {returnOrder.CustomerId}, Exists: {customerExists}");
            }
            return await _dbContext.Queryable<returnordermodel>()
                .LeftJoin<suppliermodel>((ro, s) => ro.SupplierId == s.Id)
                .LeftJoin<Sys_Department>((ro, s, d) => ro.DeptId == SqlFunc.ToString(d.DepartmentId))
                .LeftJoin<customermodel>((ro, s, d, c) => ro.CustomerId == c.Id)
                .Where((ro, s, d, c) => ro.Id == id && ro.IsDelete == 0)
                .Select((ro, s, d, c) => new ReturnOrderWithRelations
                {
                    // 退货单字段
                    Id = ro.Id,
                    ReturnOrderCode = ro.ReturnOrderCode,
                    ReturnOrderDate = ro.ReturnOrderDate,
                    PurchaseOrderCode = ro.PurchaseOrderCode,
                    SupplierId = ro.SupplierId,
                    Contact = ro.Contact,
                    ContactPhone = ro.ContactPhone,
                    DeptId = ro.DeptId,
                    CustomerId = ro.CustomerId,
                    Status = ro.Status,
                    AuditUser = ro.AuditUser,
                    AuditTime = ro.AuditTime,
                    ReturnDesc = ro.ReturnDesc,
                    Remarks = ro.Remarks,
                    Creator = ro.Creator,
                    CreateDate = ro.CreateDate,
                    Modifier = ro.Modifier,
                    ModifyDate = ro.ModifyDate,
                    IsDelete = ro.IsDelete,
                    CreateID = ro.CreateID,
                    ModifyID = ro.ModifyID,
                    
                    // 关联表字段
                    SupplierName = s.SupplierName,
                    DepartmentName = d.DepartmentName,
                    CustomerName = c.CustomerName
                })
                .FirstAsync();
        }

        /// <summary>
        /// 分页查询退货单联查结果（包含供应商名称、部门名称、客户名称）
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="whereExpression">查询条件</param>
        /// <returns>分页结果</returns>
        public async Task<(List<ReturnOrderWithRelations> Data, int TotalCount)> GetPageListWithRelationsAsync(
            int pageIndex, 
            int pageSize, 
            Expression<Func<returnordermodel, bool>> whereExpression = null)
        {
            var query = _dbContext.Queryable<returnordermodel>()
                .LeftJoin<suppliermodel>((ro, s) => ro.SupplierId == s.Id)
                .LeftJoin<Sys_Department>((ro, s, d) => ro.DeptId == SqlFunc.ToString(d.DepartmentId))
                .LeftJoin<customermodel>((ro, s, d, c) => ro.CustomerId == c.Id)
                .Where((ro, s, d, c) => ro.IsDelete == 0);
                
            if (whereExpression != null)
            {
                query = query.Where(whereExpression);
            }

            RefAsync<int> totalCount = 0;
            var data = await query
                .OrderByDescending((ro, s, d, c) => ro.CreateDate)
                .Select((ro, s, d, c) => new ReturnOrderWithRelations
                {
                    // 退货单字段
                    Id = ro.Id,
                    ReturnOrderCode = ro.ReturnOrderCode,
                    ReturnOrderDate = ro.ReturnOrderDate,
                    PurchaseOrderCode = ro.PurchaseOrderCode,
                    SupplierId = ro.SupplierId,
                    Contact = ro.Contact,
                    ContactPhone = ro.ContactPhone,
                    DeptId = ro.DeptId,
                    CustomerId = ro.CustomerId,
                    Status = ro.Status,
                    AuditUser = ro.AuditUser,
                    AuditTime = ro.AuditTime,
                    ReturnDesc = ro.ReturnDesc,
                    Remarks = ro.Remarks,
                    Creator = ro.Creator,
                    CreateDate = ro.CreateDate,
                    Modifier = ro.Modifier,
                    ModifyDate = ro.ModifyDate,
                    IsDelete = ro.IsDelete,
                    CreateID = ro.CreateID,
                    ModifyID = ro.ModifyID, 
                    OutStatus = ro.OutStatus,
                    // 关联表字段
                    SupplierName = s.SupplierName,
                    DepartmentName = d.DepartmentName,
                    CustomerName = c.CustomerName
                })
                .ToPageListAsync(pageIndex, pageSize, totalCount);
                
            return (data, totalCount);
        }
        /// <summary>
        /// 使用事务添加退货单和退货明细（使用DTO）
        /// </summary>
        /// <param name="createDto">退货单和退货明细创建DTO</param>
        /// <returns>添加结果</returns>
        public async Task<bool> AddWithDetailsAsync(ReturnOrderWithDetailsCreateDto createDto)
        {
            try
            {
                Console.WriteLine("=== Repository层开始处理（事务版本） ===");
                
                var result = await _dbContext.Ado.UseTranAsync(async () =>
                {
                    Console.WriteLine("开始事务处理");
                    
                    // 1. 创建退货单实体并设置完整的系统字段
                    var returnOrder = new returnordermodel
                    {
                        // 业务字段
                        ReturnOrderCode = createDto.ReturnOrder.ReturnOrderCode,
                        ReturnOrderDate = createDto.ReturnOrder.ReturnOrderDate,
                        PurchaseOrderCode = createDto.ReturnOrder.PurchaseOrderCode,
                        SupplierId = createDto.ReturnOrder.SupplierId,
                        Contact = createDto.ReturnOrder.Contact,
                        ContactPhone = createDto.ReturnOrder.ContactPhone,
                        DeptId = createDto.ReturnOrder.DeptId,
                        CustomerId = createDto.ReturnOrder.CustomerId,
                        Status = createDto.ReturnOrder.Status ?? 1, // 默认为草稿状态
                        ReturnDesc = createDto.ReturnOrder.ReturnDesc,
                        Remarks = createDto.ReturnOrder.Remarks ?? "",
                        
                        // 系统字段 - 后端自动生成
                        CreateDate = DateTime.Now,
                        IsDelete = 0,
                        Creator = "cjh",
                        CreateID = 666,
                        InOutTypeId=2,
                        OutStatus = 1
                    };

                    Console.WriteLine($"准备插入退货单: {returnOrder.ReturnOrderCode}");
                    
                    // 2. 插入退货单并获取自增ID
                    var returnOrderId = await _dbContext.Insertable(returnOrder)
                        .ExecuteReturnBigIdentityAsync();

                    Console.WriteLine($"退货单插入成功，ID: {returnOrderId}");

                    // 3. 创建退货明细实体列表并设置完整的系统字段
                    var returnOrderDetails = createDto.ReturnOrderDetails.Select(dto => new returnorderdetailsmodel
                    {
                        // 业务字段
                        ReturnOrderId = returnOrderId, // 设置退货单外键
                        PurchaseOrderItemId = dto.PurchaseOrderItemId,
                        ReturnNumber = dto.ReturnNumber,
                        ReturnMoney = dto.ReturnMoney,
                        Remarks = dto.Remarks ?? "",

                        // 系统字段 - 后端自动生成
                        CreateDate = DateTime.Now,
                        IsDelete = 0,
                        Creator = "cjh",
                        CreateID = 666,
                    }).ToList();

                    Console.WriteLine($"准备批量插入 {returnOrderDetails.Count} 条退货明细");

                    // 4. 批量插入退货明细（使用自增ID）
                    var detailResult = await _dbContext.Insertable(returnOrderDetails)
                        .ExecuteCommandAsync();

                    Console.WriteLine($"退货明细插入结果: {detailResult} 条");

                    // 5. 检查插入结果
                    if (detailResult <= 0)
                    {
                        Console.WriteLine("❌ 退货明细插入失败");
                        throw new Exception("退货明细插入失败");
                    }

                    Console.WriteLine("✅ 事务执行成功");
                    return true;
                });
                
                Console.WriteLine($"事务最终结果: IsSuccess={result.IsSuccess}, Data={result.Data}");
                
                if (result.IsSuccess && result.Data)
                {
                    Console.WriteLine("✅ 退货单和明细添加成功");
                    return true;
                }
                else
                {
                    Console.WriteLine("❌ 事务执行失败");
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Repository层异常: {ex.Message}");
                if (ex.InnerException != null)
                {
                    Console.WriteLine($"❌ 内部异常: {ex.InnerException.Message}");
                }
                Console.WriteLine($"❌ 异常堆栈: {ex.StackTrace}");
                return false;
            }
        }
    }
}